<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>LIMIT and OFFSET</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Queries"
HREF="queries.html"><LINK
REL="PREVIOUS"
TITLE="Sorting Rows"
HREF="queries-order.html"><LINK
REL="NEXT"
TITLE="VALUES Lists"
HREF="queries-values.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Sorting Rows"
HREF="queries-order.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="queries.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 7. Queries</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="VALUES Lists"
HREF="queries-values.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="QUERIES-LIMIT"
>7.6. <TT
CLASS="LITERAL"
>LIMIT</TT
> and <TT
CLASS="LITERAL"
>OFFSET</TT
></A
></H1
><P
>   <TT
CLASS="LITERAL"
>LIMIT</TT
> and <TT
CLASS="LITERAL"
>OFFSET</TT
> allow you to retrieve just
   a portion of the rows that are generated by the rest of the query:
</P><PRE
CLASS="SYNOPSIS"
>SELECT <TT
CLASS="REPLACEABLE"
><I
>select_list</I
></TT
>
    FROM <TT
CLASS="REPLACEABLE"
><I
>table_expression</I
></TT
>
    [<SPAN
CLASS="OPTIONAL"
> ORDER BY ... </SPAN
>]
    [<SPAN
CLASS="OPTIONAL"
> LIMIT { <TT
CLASS="REPLACEABLE"
><I
>number</I
></TT
> | ALL } </SPAN
>] [<SPAN
CLASS="OPTIONAL"
> OFFSET <TT
CLASS="REPLACEABLE"
><I
>number</I
></TT
> </SPAN
>]</PRE
><P>
  </P
><P
>   If a limit count is given, no more than that many rows will be
   returned (but possibly less, if the query itself yields less rows).
   <TT
CLASS="LITERAL"
>LIMIT ALL</TT
> is the same as omitting the <TT
CLASS="LITERAL"
>LIMIT</TT
>
   clause.
  </P
><P
>   <TT
CLASS="LITERAL"
>OFFSET</TT
> says to skip that many rows before beginning to
   return rows.  <TT
CLASS="LITERAL"
>OFFSET 0</TT
> is the same as omitting the
   <TT
CLASS="LITERAL"
>OFFSET</TT
> clause, and <TT
CLASS="LITERAL"
>LIMIT NULL</TT
> is the same
   as omitting the <TT
CLASS="LITERAL"
>LIMIT</TT
> clause.  If both <TT
CLASS="LITERAL"
>OFFSET</TT
>
   and <TT
CLASS="LITERAL"
>LIMIT</TT
> appear, then <TT
CLASS="LITERAL"
>OFFSET</TT
> rows are
   skipped before starting to count the <TT
CLASS="LITERAL"
>LIMIT</TT
> rows that
   are returned.
  </P
><P
>   When using <TT
CLASS="LITERAL"
>LIMIT</TT
>, it is important to use an
   <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause that constrains the result rows into a
   unique order.  Otherwise you will get an unpredictable subset of
   the query's rows. You might be asking for the tenth through
   twentieth rows, but tenth through twentieth in what ordering? The
   ordering is unknown, unless you specified <TT
CLASS="LITERAL"
>ORDER BY</TT
>.
  </P
><P
>   The query optimizer takes <TT
CLASS="LITERAL"
>LIMIT</TT
> into account when
   generating query plans, so you are very likely to get different
   plans (yielding different row orders) depending on what you give
   for <TT
CLASS="LITERAL"
>LIMIT</TT
> and <TT
CLASS="LITERAL"
>OFFSET</TT
>.  Thus, using
   different <TT
CLASS="LITERAL"
>LIMIT</TT
>/<TT
CLASS="LITERAL"
>OFFSET</TT
> values to select
   different subsets of a query result <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>will give
   inconsistent results</I
></SPAN
> unless you enforce a predictable
   result ordering with <TT
CLASS="LITERAL"
>ORDER BY</TT
>.  This is not a bug; it
   is an inherent consequence of the fact that SQL does not promise to
   deliver the results of a query in any particular order unless
   <TT
CLASS="LITERAL"
>ORDER BY</TT
> is used to constrain the order.
  </P
><P
>   The rows skipped by an <TT
CLASS="LITERAL"
>OFFSET</TT
> clause still have to be
   computed inside the server; therefore a large <TT
CLASS="LITERAL"
>OFFSET</TT
>
   might be inefficient.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="queries-order.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="queries-values.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Sorting Rows</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="queries.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><TT
CLASS="LITERAL"
>VALUES</TT
> Lists</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>